/**
 * 
 */
package com.srini.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import com.vssr.persist.exception.DomainException;



/**
 * @author Srini
 */
public class JDBCDao {

	public static void saveObject(Object object) {

	}

	public static List fetchRecords(String query, Connection conn) {

		PreparedStatement stmt = null;
		ResultSet resultSet = null;

		List list = new ArrayList();
		try {
			stmt = conn.prepareStatement(query);
			resultSet = stmt.executeQuery();

			ResultSetMetaData metaData = resultSet.getMetaData();
			int columnCount = metaData.getColumnCount();

			while (resultSet.next()) {
				int i = 1;

				List aList = new ArrayList();
				while (i <= columnCount) {
					if (resultSet.getObject(i) != null) {

						Object valueObj = resultSet.getObject(i);
						String value = null;
						if (valueObj instanceof java.sql.Date) // since all
						{
							SimpleDateFormat formatter = new SimpleDateFormat(
									"MM-dd-yyyy");
							value = formatter.format((java.util.Date) valueObj);
							aList.add(value);

						} else {
							aList.add(valueObj.toString());
						}
					} else {
						aList.add("");
					}
					i++;
				}
				list.add(aList);
			}
		} catch (SQLException sqlExp) {
			// throw new SQLException();
			sqlExp.printStackTrace();
		} finally {
			try {
				if (stmt != null) {
					stmt.close();
				}

				if (resultSet != null) {
					resultSet.close();
				}
			} catch (SQLException ex) {
				// throw new SQLException();
				ex.printStackTrace();
			}
		}
		return list;

	}

	/**
	 * 
	 * @param tableName
	 * @param data
	 * @param isIdAutoGenerated
	 *            Set this as true if table id is autogenerated
	 * @param conn
	 * @throws DomainException
	 */
	public static Object persistRecords(String tableName,
			List<List<Object>> data, boolean isIdAutoGenerated, Connection conn)
			throws DomainException {
		Object generatedId = null;
		try {
			if (conn == null) {
				throw new DomainException("Connection object cannot be null");
			}
			if (data == null) {
				throw new DomainException("No data exists to Persist");
			}

			StringBuffer query = null;
			for (List<Object> element : data) {
				Iterator itr = element.iterator();
				query = new StringBuffer("insert into ");
				query.append(tableName).append(" values (");
				if (isIdAutoGenerated) {
					query.append("DEFAULT, ");
				}
				while (itr.hasNext()) {
					Object obj = itr.next();
					query.append(getFormattedData(obj));
					if (itr.hasNext()) {
						query.append(",");
					}
				}
				query.append(")");
				PreparedStatement stmt = conn.prepareStatement(
						query.toString(), Statement.RETURN_GENERATED_KEYS);
				stmt.executeUpdate();
				if (isIdAutoGenerated) {
					ResultSet rs = stmt.getGeneratedKeys();
					while (rs.next()) {
						generatedId = rs.getString(1);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return generatedId;
	}

	public static void saveRecords(String tableName, List<List<Object>> data,
			boolean isIdAutoGenerated, Connection conn) throws SQLException {

		String colsNvalues = getTableColumns(tableName);

		String str = "insert into " + tableName + colsNvalues;
		try {
			int i = 0;
			PreparedStatement pstmt = conn.prepareStatement(str,
					Statement.RETURN_GENERATED_KEYS);
			for (List<Object> element : data) {
				i = 1;
				Iterator itr = element.iterator();
				if (isIdAutoGenerated) {
					pstmt.setObject(i, Statement.RETURN_GENERATED_KEYS);
					i++;
				}
				while (itr.hasNext()) {
					Object obj = itr.next();
					setPreparedData(i, obj, pstmt);
					i++;
				}
				pstmt.addBatch();
			}
			int[] x = pstmt.executeBatch();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static String getTableColumns(String tableName) throws SQLException {
		int i = 2;
		PreparedStatement stmt = new ConnectionUtil().getConnection()
				.prepareStatement("select * from " + tableName);
		ResultSet rs = stmt.executeQuery();
		StringBuffer columns = new StringBuffer("(");
		StringBuffer values = new StringBuffer(" values (");
		ResultSetMetaData rsmd = rs.getMetaData();
		for (; i < rsmd.getColumnCount(); i++) {
			columns.append(rsmd.getColumnName(i)).append(",");
			values.append("?,");
		}
		columns.append(rsmd.getColumnName(i)).append(")");
		values.append("?)");

		return columns.append(values).toString();
	}

	private static void setPreparedData(int i, Object obj,
			PreparedStatement pstmt) throws SQLException {
		Class c = obj.getClass();
		if (c == Integer.class) {
			int x = Integer.valueOf(obj.toString()).intValue();
			pstmt.setInt(i, x);
		} else if (c == Date.class || c == java.sql.Date.class) {
			Date d = new Date(obj.toString());
			Calendar cal = Calendar.getInstance();
			cal.setTime(d);
			pstmt.setDate(i, new java.sql.Date(cal.getTimeInMillis()));
		} else if (obj instanceof InputStream) {
			InputStream is = InputStream.class.cast(obj);
			pstmt.setAsciiStream(i, is);
		} else {
			pstmt.setString(i, obj.toString());
		}

	}

	public static void updateRecords(String tableName, List dataList,
			String whereColumnName, Object value, Connection conn)
			throws DomainException {
		try {
			if (conn == null) {
				throw new DomainException("Connection object cannot be null");
			}
			if (dataList == null) {
				throw new DomainException("No data exists to Persist");
			}
			StringBuffer query = new StringBuffer("select * from ");
			query.append(tableName);

			Statement stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query.toString());
			ResultSetMetaData rsmd = rs.getMetaData();
			query = new StringBuffer();
			query.append("update ").append(tableName);
			query.append(" set ");

			int i = 1;

			Iterator itr = dataList.iterator();
			while (itr.hasNext()) {
				Object obj = itr.next();
				Class cls = obj.getClass();
				query.append(rsmd.getColumnName(i)).append("=");
				query.append(getFormattedData(obj));
				if (itr.hasNext()) {
					query.append(",");
				}
				i++;
			}
			query.append(" where ").append(whereColumnName).append("=");
			query.append(getFormattedData(value));
			stmt.executeUpdate(query.toString());
			conn.commit();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static Object getFormattedData(Object obj) {
		if (obj == null) {
			return "''";
		}
		StringBuffer query = new StringBuffer();
		Class cls = obj.getClass();
		if (cls == Integer.class || cls == Long.class) {
			query.append(obj);
		} else if (cls == Date.class || cls == java.sql.Date.class) {
			query.append("toDate('yyyy-mm-dd', ");
			query.append("'").append(obj).append("')");
		} else {
			if (obj.toString().contains("'")) {
				obj = obj.toString().replace("'", "''");
			}
			query.append("'").append(obj).append("'");
		}

		return query;
	}

	public static void deleteRecord(String tableName, String whereColumnName,
			Object value, Connection conn) {
		try {
			StringBuffer query = new StringBuffer("delete from ");
			query.append(tableName);
			if (whereColumnName != null && !whereColumnName.isEmpty()) {
				query.append(" where ").append(whereColumnName).append("=");
				query.append(getFormattedData(value));
			}
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query.toString());
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void deleteRecord(String tableName, String[] whereColumnName,
			Object[] value, Connection conn) {
		try {
			StringBuffer query = new StringBuffer("delete from ");
			query.append(tableName);
			int i = 0;
			query.append(" where ");
			if (whereColumnName != null && whereColumnName.length > 0) {
				for (; i < value.length - 1; i++) {
					query.append(whereColumnName[i]).append("=");
					query.append(getFormattedData(value[i]));
					query.append(" and ");
				}

				query.append(whereColumnName[i]).append("=");
				query.append(getFormattedData(value[i]));
			}
			System.out.println(query);
			Statement stmt = conn.createStatement();
			stmt.executeUpdate(query.toString());
			conn.commit();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
